from datetime import datetime
from typing import Optional
from fastapi import APIRouter, Query
from pydantic import BaseModel
from tortoise.expressions import Q

from apps.models import Coach, User, User_Profiles
from tortoise.transactions import in_transaction
from fastapi import HTTPException
coaches = APIRouter()

class CoachProfileResponse(BaseModel):
    coach_id: int
    fullname: str
    gender: str
    birthday: Optional[datetime]
    address: Optional[str]
    username: str  # 来自 User 表
    phone: str     # 来自 User 表

    class Config:
        orm_mode = True

class CoachCreateRequest(BaseModel):
    username: str
    password: str
    email: Optional[str]
    phone: str
    fullname: str
    gender: str
    id_number: str
    license_number: str
    birthday: Optional[datetime]
    address: Optional[str]
    hire_date: Optional[datetime]
    years_of_experience: int
    specialization: str

class CoachUpdateRequest(BaseModel):
    username: Optional[str] = None
    password: Optional[str] = None
    email: Optional[str] = None
    phone: Optional[str] = None
    fullname: Optional[str] = None
    gender: Optional[str] = None
    birthday: Optional[datetime] = None
    address: Optional[str] = None
    hire_date: Optional[datetime] = None
    years_of_experience: Optional[int] = None
    specialization: Optional[str] = None

@coaches.get("/msg")
async def get_all_coaches(
    username: Optional[str] = Query(None, alias="username"),
    page: int = Query(1, ge=1),
    page_size: int = Query(10, ge=1, le=100)
):
    # 构建基础查询
    query = Coach.all().select_related('user_id')
    # 如果提供了 username，进行模糊匹配
    if username:
        query = query.filter(
            user_id__username__icontains=username
        )
    # 获取总数量
    total = await query.count()
    # 分页查询
    coaches = await query.offset((page - 1) * page_size).limit(page_size)

    data = []

    for coach in coaches:
        profile = await User_Profiles.get_or_none(user_id=coach.user_id.user_id)
        if not profile:
            continue

        data.append({
            "coach_id": coach.coach_id,
            "fullname": profile.fullname,
            "gender": profile.gender,
            "email": coach.user_id.email,
            "specialization": coach.specialization,
            "license_number": coach.license_number,
            "username": coach.user_id.username,
            "phone": coach.user_id.phone,
            "rating":coach.rating,
            "years_of_experience": coach.years_of_experience,
            "birthday": profile.birthday,
            "address": profile.address
        })

    return {
        "status": 200,
        "message": "success",
        "data": data,
        "total": total,
        "page": page,
        "page_size": page_size
    }


# 学员端使用
# 学员端使用
@coaches.get("/msg/msg")
async def get_all_coaches(
        search: Optional[str] = Query(None, description="搜索关键字"),
        page: int = Query(1, ge=1, description="页码"),
        page_size: int = Query(10, ge=1, le=100, description="每页数量")
):
    # 构建基础查询
    query = Coach.all().select_related('user_id')
    # 如果提供了搜索关键字，进行模糊匹配（只根据姓名搜索）
    if search:
        query = query.filter(
            user_id__profiles__fullname__icontains=search  # 只按全名搜索
        )

    # 获取总数量
    total = await query.count()
    print(f"Total number of coaches: {total}")

    # 分页查询
    coaches = await query.offset((page - 1) * page_size).limit(page_size)

    data = []

    for coach in coaches:
        profile = await User_Profiles.get_or_none(user_id=coach.user_id.user_id)
        if not profile:
            continue

        data.append({
            "coach_id": coach.coach_id,
            "fullname": profile.fullname,
            "gender": profile.gender,
            "email": coach.user_id.email,
            "specialization": coach.specialization,
            "license_number": coach.license_number,
            "username": coach.user_id.username,
            "phone": coach.user_id.phone,
            "rating": coach.rating,
            "years_of_experience": coach.years_of_experience,
            "birthday": profile.birthday,
            "address": profile.address
        })

    return {
        "status": 200,
        "message": "success",
        "data": {
            "list": data,
            "total": total,
            "page": page,
            "page_size": page_size
        }
    }




@coaches.get("/msg/{coach_id}")
async def get_coach(coach_id: int):
    coach = await Coach.get_or_none(coach_id=coach_id).select_related('user_id')
    if not coach:
        return {"status": 404, "message": "Coach not found"}

    profile = await User_Profiles.get_or_none(user_id=coach.user_id.user_id)
    if not profile:
        return {"status": 404, "message": "Profile not found for this coach"}

    return {
        "status": 200,
        "message": "success",
        "data": {
            "coach_id": coach.coach_id,
            "password": coach.user_id.password,
            "hire_date":coach.hire_date,
            "fullname": profile.fullname,
            "email": coach.user_id.email,
            "gender": profile.gender,
            "rating": coach.rating,
            "license_number": coach.license_number,
            "username": coach.user_id.username,
            "phone": coach.user_id.phone,
            "birthday": profile.birthday,
            "address": profile.address,
            "years_of_experience": coach.years_of_experience,
            "specialization": coach.specialization,
        }
    }


@coaches.get("/msg/userId/{user_id}")
async def get_coachByuserId(user_id: int):
    coach = await Coach.get_or_none(user_id=user_id).select_related('user_id')
    if not coach:
        return {"status": 404, "message": "Coach not found"}

    profile = await User_Profiles.get_or_none(user_id=coach.user_id.user_id)
    if not profile:
        return {"status": 404, "message": "Profile not found for this coach"}

    return {
        "status": 200,
        "message": "success",
        "data": {
            "coach_id": coach.coach_id,
            "fullname": profile.fullname,
            "hire_date":coach.hire_date,
            "license_number":coach.license_number,
            "years_of_experience": coach.years_of_experience,
            "specialization": coach.specialization,
            "username": coach.user_id.username,
        }
    }
@coaches.post("/add")
async def create_coach(data: CoachCreateRequest):
    # 检查用户名是否存在
    existing_user = await User.get_or_none(username=data.username)
    if existing_user:
        return {"status": 400, "message": "Username already exists"}

    try:
        async with in_transaction() as connection:
            # 创建 User
            user = await User.create(
                username=data.username,
                password=data.password,
                email=data.email,
                phone=data.phone,
                role="COACH",
                using_db=connection
            )

            # 创建 User_Profile - 传递user对象而不是user.user_id
            await User_Profiles.create(
                user_id=user,  # 这里改为传递user对象
                fullname=data.fullname,
                gender=data.gender,
                id_number=data.id_number,
                birthday=data.birthday,
                address=data.address,
                using_db=connection
            )

            # 如果 hire_date 未提供，则设置为当前时间
            hire_date = data.hire_date if data.hire_date is not None else datetime.now()

            # 创建 Coach - 传递user对象而不是user.user_id
            await Coach.create(
                user_id=user,  # 这里改为传递user对象
                hire_date=hire_date,
                license_number=data.license_number,
                years_of_experience=data.years_of_experience,
                specialization=data.specialization,
                using_db=connection
            )

        return {"status": 201, "message": "Coach created successfully"}

    except Exception as e:
        return {"status": 500, "message": f"Error creating coach: {str(e)}"}

@coaches.patch("/update/{coach_id}")  # 改为 PATCH
async def update_coach_partial(coach_id: int, data: CoachUpdateRequest):
    coach = await Coach.get_or_none(coach_id=coach_id)
    if not coach:
        raise HTTPException(status_code=404, detail="Coach not found")

    user = await User.get_or_none(user_id=coach.user_id_id)
    if not user:
        raise HTTPException(status_code=404, detail="User not found")

    profile = await User_Profiles.get_or_none(user_id=user.user_id)
    if not profile:
        raise HTTPException(status_code=404, detail="Profile not found")

    update_data = data.dict(exclude_unset=True)  # 只包含已设置的字段

    # 分别更新不同表
    if "username" in update_data or "password" in update_data or "email" in update_data or "phone" in update_data:
        await user.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ["username", "password", "email", "phone"]
        }).save()

    if "fullname" in update_data or "gender" in update_data or "birthday" in update_data or "address" in update_data:
        await profile.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ["fullname", "gender", "birthday", "address"]
        }).save()

    if "hire_date" in update_data or "license_number" in update_data or "years_of_experience" in update_data or "specialization" in update_data:
        await coach.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ["hire_date", "license_number", "years_of_experience", "specialization"]
        }).save()

    return {"status": 200, "message": "Coach updated successfully"}

@coaches.put("/update/{coach_id}")
async def update_coach(coach_id: int, data: CoachUpdateRequest):
    # 查询 Coach 并预加载关联的 User 和 User_Profiles
    coach = await Coach.get_or_none(coach_id=coach_id)
    if not coach:
        return {"status": 404, "message": "Coach not found"}
    # 加载关联的 User
    user = await User.get_or_none(user_id=coach.user_id_id)
    if not user:
        return {"status": 404, "message": "User not found"}

    # 加载关联的 User_Profiles
    profile = await User_Profiles.get_or_none(user_id=user.user_id)
    if not profile:
        return {"status": 404, "message": "Profile not found"}

    update_data = data.dict(exclude_unset=True)  # 只获取提供的字段

    # 更新 User 表
    if any(k in update_data for k in ['username', 'password', 'email', 'phone']):
        if 'username' in update_data:
            # 检查新用户名是否已被占用（排除当前用户）
            existing_user = await User.get_or_none(username=update_data['username'])
            if existing_user and existing_user.user_id != user.user_id:
                return {"status": 400, "message": "Username already exists"}

        # 使用 update_from_dict 安全更新字段
        await user.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ['username', 'password', 'email', 'phone']
        }).save()

    # 更新 User_Profiles 表
    if any(k in update_data for k in ['fullname', 'gender', 'birthday', 'address']):
        await profile.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ['fullname', 'gender', 'birthday', 'address']
        }).save()

    # 更新 Coach 表
    if any(k in update_data for k in ['hire_date','license_number', 'years_of_experience', 'specialization']):
        await coach.update_from_dict({
            k: v for k, v in update_data.items()
            if k in ['hire_date','license_number', 'years_of_experience', 'specialization']
        }).save()

    return {"status": 200, "message": "Coach updated successfully"}

@coaches.delete("/delete/{coach_id}")
async def delete_coach(coach_id: int):
    coach = await Coach.get_or_none(coach_id=coach_id)
    if not coach:
        return {"status": 404, "message": "Coach not found"}
    user_id = coach.user_id_id
    # 删除关联的 User_Profiles
    await User_Profiles.filter(user_id=user_id).delete()
    # 删除关联的 User
    await User.filter(user_id=user_id).delete()

    await coach.delete()
    return {"status": 200, "message": "Coach deleted successfully"}
